﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;

namespace JobDescOnline.Presentation.Models
{
    public static class Accounts
    {
        public static JobDescDBEntities1 db = new JobDescDBEntities1();
        public static dynamic RetrieveByFilterAndPage(List<FilterData> filters, int page, int pagesize, string sort, string direction)
        {
            var _temp = from a in db.Accounts
                        join s in db.Sections on a.SectionID equals s.ID
                        join j in db.JobTitles on a.JobTitleID equals j.ID
                        join r in db.Roles on a.RoleID equals r.ID
                        select new AccountSectionJobTitle
                        {
                            ID = a.ID,
                            Rowstatus = a.Rowstatus,
                            Fullname = a.Fullname,
                            Email = a.Email,
                            SectionName = s.Description,
                            JobTitleName = j.Description,
                            TTD = a.TTD,
                            RoleName = r.RoleName,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (direction.ToLower() == "asc")
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderBy(a => a.ID); break;
                    case "Fullname": _temp = _temp.OrderBy(a => a.Fullname); break;
                    case "Email": _temp = _temp.OrderBy(a => a.Email); break;
                    case "Section": _temp = _temp.OrderBy(s => s.SectionName); break;
                    case "JobTitle": _temp = _temp.OrderBy(j => j.JobTitleName); break;
                    case "Role": _temp = _temp.OrderBy(j => j.RoleName); break;
                }
            }
            else
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderByDescending(a => a.ID); break;
                    case "Fullname": _temp = _temp.OrderByDescending(a => a.Fullname); break;
                    case "Email": _temp = _temp.OrderByDescending(a => a.Email); break;
                    case "Section": _temp = _temp.OrderByDescending(s => s.SectionName); break;
                    case "JobTitle": _temp = _temp.OrderByDescending(j => j.JobTitleName); break;
                    case "Role": _temp = _temp.OrderByDescending(j => j.RoleName); break;
                }
            }

            if (filters.Count > 0)
            {
                foreach (FilterData filter in filters)
                {
                    FilterData copy = filter;
                    switch (filter.column)
                    {
                        case "Fullname": _temp = _temp.Where(a => a.Fullname.Contains(copy.value)); break;
                        case "Email": _temp = _temp.Where(a => a.Email.Contains(copy.value)); break;
                        case "Section": _temp = _temp.Where(s => s.SectionName.Contains(copy.value)); break;
                        case "JobTitle": _temp = _temp.Where(j => j.JobTitleName.Contains(copy.value)); break;
                        case "Role": _temp = _temp.Where(j => j.RoleName.Contains(copy.value)); break;
                    }
                }
            }

            return _temp.Skip((page - 1) * pagesize).Take(pagesize).ToList();
        }

        public static dynamic RetrieveByFilterAndPage(int page, int pagesize, string sort, string direction)
        {
            var _temp = from a in db.Accounts
                        join s in db.Sections on a.SectionID equals s.ID
                        join j in db.JobTitles on a.JobTitleID equals j.ID
                        join r in db.Roles on a.RoleID equals r.ID
                        select new AccountSectionJobTitle
                        {
                            ID = a.ID,
                            Rowstatus = a.Rowstatus,
                            Fullname = a.Fullname,
                            Email = a.Email,
                            SectionName = s.Description,
                            JobTitleName = j.Description,
                            TTD = a.TTD,
                            RoleName = r.RoleName,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (direction.ToLower() == "asc")
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderBy(a => a.ID); break;
                    case "Fullname": _temp = _temp.OrderBy(a => a.Fullname); break;
                    case "Email": _temp = _temp.OrderBy(a => a.Email); break;
                    case "Section": _temp = _temp.OrderBy(s => s.SectionName); break;
                    case "JobTitle": _temp = _temp.OrderBy(j => j.JobTitleName); break;
                    case "Role": _temp = _temp.OrderBy(j => j.RoleName); break;
                }
            }
            else
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderByDescending(a => a.ID); break;
                    case "Fullname": _temp = _temp.OrderByDescending(a => a.Fullname); break;
                    case "Email": _temp = _temp.OrderByDescending(a => a.Email); break;
                    case "Section": _temp = _temp.OrderByDescending(s => s.SectionName); break;
                    case "JobTitle": _temp = _temp.OrderByDescending(j => j.JobTitleName); break;
                    case "Role": _temp = _temp.OrderByDescending(j => j.RoleName); break;
                }
            }

            return _temp.Skip((page - 1) * pagesize).Take(pagesize).ToList();
        }

        public static int RetrieveByFilter(List<FilterData> filters)
        {
            var _temp = from a in db.Accounts
                        join s in db.Sections on a.SectionID equals s.ID
                        join j in db.JobTitles on a.JobTitleID equals j.ID
                        join r in db.Roles on a.RoleID equals r.ID
                        select new AccountSectionJobTitle
                        {
                            ID = a.ID,
                            Rowstatus = a.Rowstatus,
                            Fullname = a.Fullname,
                            Email = a.Email,
                            SectionName = s.Description,
                            JobTitleName = j.Description,
                            TTD = a.TTD,
                            RoleName = r.RoleName,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (filters.Count > 0)
            {
                foreach (FilterData filter in filters)
                {
                    FilterData copy = filter;
                    switch (filter.column)
                    {
                        case "Fullname": _temp = _temp.Where(a => a.Fullname.Contains(copy.value)); break;
                        case "Email": _temp = _temp.Where(a => a.Email.Contains(copy.value)); break;
                        case "Section": _temp = _temp.Where(s => s.SectionName.Contains(copy.value)); break;
                        case "JobTitle": _temp = _temp.Where(j => j.JobTitleName.Contains(copy.value)); break;
                        case "Role": _temp = _temp.Where(j => j.RoleName.Contains(copy.value)); break;
                    }
                }
            }

            return _temp.ToList().Count;
        }

        public static int RetrieveByFilter()
        {
            var list = db.Accounts.ToList();
            return list.Count;
        }
    }

    public class AccountSectionJobTitle
    {
        public int ID { get; set; }
        public bool Rowstatus { get; set; }
        public string Fullname { get; set; }
        public string Email { get; set; }
        public string SectionName { get; set; }
        public string JobTitleName { get; set; }
        public string RoleName { get; set; }
        public string TTD { get; set; }
        public string CreatedBy { get; set; }
        public Nullable<DateTime> CreatedOn { get; set; }
        public string LastModifiedBy { get; set; }
        public Nullable<DateTime> LastModifiedOn { get; set; }
    }

    public class LogOnModel
    {
        [Required]
        [DisplayName("Email")]
        [DataType(DataType.EmailAddress)]
        public string Email { get; set; }

        [Required]
        [DataType(DataType.Password)]
        [DisplayName("Password")]
        public string Password { get; set; }

        [DisplayName("Remember me?")]
        public bool RememberMe { get; set; }
    }
}